'''
	This file contains the interfaces to required interactions with the database
		- creating database
		- creating tables
		- inserting into a table
		- updating a table
		- querying a table
	In order to change anything related to database, a change in this file is sufficient
'''


import sqlite3 as sql
import config as cg


# A simple function to connect to a database
def connectDb():
	conn = sql.connect(cg.dbUrl)
	return conn
	
# A simple function that lets you commit and close the connection	
def closeConn(conn):
	conn.commit()
	conn.close()
	

# To create the table corresponding to the Users and Revisions
def createTbUsersRev(conn):
	query = "CREATE TABLE userRev (user text PRIMARY KEY, rev text, absNum int)"
	#print query
	c = conn.cursor()
	try:
		c.execute(query)
	except:
		errno = "Unable to create the table"

# Simplifying the query - inserting into the table corresponding to the Users and Revisions
def insertTbUsersRev(conn, values):
	query = "INSERT INTO userRev VALUES( '" + values[0] + "', '" + values[1] + "', '" + str(values[2]) +  "' )"
	#print query
	c = conn.cursor()
	try:
		c.execute(query)
	except:
		errno = "Unable to add into table"
		

# We need the revision numbers in order of their absolute ids
def extractRevOrdered(conn):
	query = "SELECT rev FROM userRev ORDER BY absNum ASC";
	c = conn.cursor()
	try:
		c.execute(query)
		revList = c.fetchall()
		#print revList;

		return revList;
	except:
		print "Unable to retreive from userRev"

		
# To create table corresponding to all the revisions		
def createTbRev(conn):
	query = "CREATE TABLE Revisions (rev text, id int PRIMARY_KEY AUTO_INCREMENT)"
	#print query
	c = conn.cursor()
	try:
		c.execute(query)
	except:
		errno = "Unable to create the table"

# Simplifying the query - inserting into the table corresponding to all the revisions		
def insertTbRev(conn, value):
	query = "INSERT INTO Revisions(rev) VALUES( '" + str(value) +  "' )"
	c = conn.cursor()
	try:
		c.execute(query)
	except:
		errno =  "Unable to add into table"


# Simplifying the query - extracting the list of revisions from the database	
def extractDataTbRev(conn):
	query = "SELECT rev FROM Revisions"
	c = conn.cursor()
	try:
		c.execute(query)
		data = c.fetchall()
		return data
	except:
		errno = "Unable to retreive"

# Modifying the Revisions table by adding another column		
def modifyTbRev(conn):
	query = "ALTER TABLE Revisions ADD COLUMN Text text";
	c = conn.cursor()
	try:
		c.execute(query)
	except:
		errno =  "Unable to modify table"

# Add text corresponding to a particular revision				
def addTextTbRev(conn, rev, Text):
	#query = "UPDATE Revisions SET Text='" + Text +"' WHERE rev='"+ str(rev)+"';";
	query = "UPDATE Revisions SET Text=? WHERE rev=?";
	c = conn.cursor()
	try:
		c.execute(query, (Text, rev));
	except:
		print "Unable to upadate table"

# This functions helps retrieve text for a given revision
def extractTextForRev(conn, rev):
	query = "SELECT Text from Revisions WHERE rev='"+rev+"'";
	c = conn.cursor()
	try:
		c.execute(query);
		Text = c.fetchall();
		return Text[0];
	except:
		print "Unable to retrieve data"

def createTbWords(conn):
	query = "CREATE TABLE Words (word text PRIMARY_KEY, freq int, rev text, age int)"
	c = conn.cursor()
	try:
		c.execute(query)
	except:
		errno = "Unable to create the table"
		
def cleanTbWords(conn):
	query = "DELETE FROM Words WHERE freq='0'"
	c = conn.cursor()
	c.execute(query);
		
def initializeTbWords(conn):
	query = "UPDATE Words SET freq='0'"
	c = conn.cursor()
	c.execute(query);
	
def extractDataTbWords(conn):
	query = "SELECT * from Words";
	c = conn.cursor()
	try:
		c.execute(query)
		rows = c.fetchall()
		return rows;
	except:
		print "Unable to extract data"
	
def insertIntoTbWords(conn, word, rev):
	query = "SELECT * from Words WHERE word = '" + word + "'"
	c = conn.cursor()
	
	try:
		c.execute(query)
		row = c.fetchall()
		freq = row[0][1];
		rev1 = row[0][2];
		age = row[0][3];
		if( freq == 0 ):
			age += 1;
			freq = 1;

		else:
			freq += 1;
			
		query = "UPDATE Words SET freq=?, age=? WHERE word=?"
		freq += 1;
		try:
			c.execute(query, (freq, age,word));
		except:
			print "Unable to update"
			return;
	except:
		query = "INSERT INTO Words(word, freq, rev, age) VALUES(?,?,?,?)"
		try:
			c.execute(query, (word, '1', rev, '1'));
		except:
			print "Unable To insert"

	

# This is just a test function - to test if the other functions are working fine
def main():
	conn = connectDb()
	rev = '2458584'
	Text = extractTextForRev(conn, rev)
	print Text;
	#createTbUsersRev(conn)
	#insertTbUsersRev(conn, ['user', 'revision', '1' ])
	#insertTbUsersRev(conn, ['user', 'revision1', '2'])
	closeConn(conn)

#main()

